package yunquan.wang.test.importData;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import yunquan.wang.modules.customer.entity.Company;
import yunquan.wang.modules.customer.service.CompanyService;
import yunquan.wang.test.ServiceTest;

 
public class ImportExcel   extends ServiceTest{
     @Resource
	CompanyService companyService;
 
 	@Test
 	@Transactional("jpaTransaction")
 	@Rollback(false)
	public void testImportCompnyData() throws Exception
	{
		BufferedInputStream bf =new BufferedInputStream( new FileInputStream( "D:\\tj09_1.xls"));  
		   submethod(bf);
		   bf =new BufferedInputStream( new FileInputStream( "D:\\tj09_2.xls"));  
		   submethod(bf);
		   bf =new BufferedInputStream( new FileInputStream( "D:\\tj09_3.xls"));  
		   submethod(bf);
	}

	private void submethod(BufferedInputStream bf) throws IOException {
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(bf);   
		  
		   HSSFSheet sheet=	   hssfWorkbook.getSheetAt(0);
		   Iterator<Row>  rows=	   sheet.rowIterator();
		   rows.next();
		   List<Company> lists=new ArrayList<Company>();
		   while(rows.hasNext())
		   {
			 Row row=  rows.next();
			 Company com=new Company();
			 com.setFullName(ExcelUitls.getCellValue(row,0));
			 // ExcelUitls.getCellValue(row,1)
			 com.setManagerName(ExcelUitls.getCellValue(row,1));
			 com.setAddress(ExcelUitls.getCellValue(row,2));
			 com.setPhonenumber(ExcelUitls.getCellValue(row,4));
			 com.setTrade(ExcelUitls.getCellValue(row,5));
			 com.setMainProduct1(ExcelUitls.getCellValue(row,6));
			 com.setMainProduct2(ExcelUitls.getCellValue(row,7));
			 com.setOrgtype(ExcelUitls.getCellValue(row,9));
			String mongth= ExcelUitls.getCellValue(row,11);
			if(mongth.length()==1)mongth="0"+mongth;
			 com.setBegintime(ExcelUitls.getCellValue(row,10)+mongth);
			 com.setEmployee(Integer.valueOf(ExcelUitls.getCellValue(row,12)));
			 com.setSalesRevenue(Integer.valueOf(ExcelUitls.getCellValue(row,13)));
			   lists.add(com);
		   }
		   companyService.save(lists);
	}


}
